﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using SapB1WebPOS.Utils;
using System.Text;

namespace SapB1WebPOS.DAL
{
    public class PurchaseOrderDAO
    {
        public DataSet GetOPORForCopyGRPO(string companyDB,string cardCode )
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.PURCHASE_ORDER_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                StringBuilder query = new StringBuilder();
                query.Append("SELECT DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,DocDate,DocDueDate,TaxDate,VatSum FROM ");
                query.Append(TableNameDefined.PURCHASE_ORDER_HEADER);
                query.Append(" WHERE DocStatus ='O' AND CardCode = @Param1");
                query.Append(" ORDER BY DocEntry DESC ");

                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(cardCode);

                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOPORForCopySearch(string companyDB, string keySearch,string cardCode)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.PURCHASE_ORDER_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(keySearch);
                lstParamValue.Add(cardCode);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.PURCHASE_ORDER_HEADER + " T0 JOIN " + TableNameDefined.PURCHASE_ORDER_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  WHERE T0.DocEntry LIKE '%' + @Param1 + '%' AND T0.CardCode = @Param2 AND T0.DocStatus = 'O' ");
                query.Append("  GROUP BY T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry ASC  ");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOPOR(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.PURCHASE_ORDER_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                StringBuilder query = new StringBuilder();
                query.Append("SELECT DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,DocDate,DocDueDate,TaxDate,VatSum FROM ");
                query.Append(TableNameDefined.PURCHASE_ORDER_HEADER);
                query.Append(" ORDER BY DocEntry DESC ");
                dt = connect.Excute_Query(query.ToString(), null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOPORByDocEntry(string companyDB, string docEntry)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.PURCHASE_ORDER_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(docEntry);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.PURCHASE_ORDER_HEADER + " T0 JOIN " + TableNameDefined.PURCHASE_ORDER_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  WHERE T0.DocEntry = @Param1");
                query.Append("  GROUP BY T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry ASC  ");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOPORForSearch(string companyDB, string keySearch)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.PURCHASE_ORDER_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(keySearch);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.PURCHASE_ORDER_HEADER + " T0 JOIN " + TableNameDefined.PURCHASE_ORDER_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  WHERE T0.DocEntry LIKE '%' + @Param1 + '%'");
                query.Append("  GROUP BY T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry ASC  ");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        public DataSet GetOPORLastRecord(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.PURCHASE_ORDER_HEADER);
                SQLConnection connect = new SQLConnection(companyDB);
                StringBuilder query = new StringBuilder();
                query.Append("  SELECT TOP 1 T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum , SUM(LineTotal) AS SubTotal");
                query.Append("  FROM " + TableNameDefined.PURCHASE_ORDER_HEADER + " T0 JOIN " + TableNameDefined.PURCHASE_ORDER_LINE + " T1 ON T0.DocEntry  =  T1.DocEntry ");
                query.Append("  GROUP BY T0.DocEntry, DocNum,DocStatus,CANCELED,CardCode,CardName,DocTotal,Comments,T0.DocDate,T0.DocDueDate,TaxDate,T0.VatSum ");
                query.Append("  ORDER BY T0.DocEntry DESC ");

                dt = connect.Excute_Query(query.ToString(), null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }

        public DataSet GetPOR1ByDocEntry(string companyDB, string docEntry)
        {
            try
            {
                DataSet dt = new DataSet(TableNameDefined.PURCHASE_ORDER_LINE);
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(docEntry);
                StringBuilder query = new StringBuilder();
                query.Append("SELECT  OWHS.WhsName,ItemCode ,Dscription ,PriceBefDi ,DiscPrcnt ,Price ,unitMsr, Quantity,  LineTotal, T0.VatGroup, VatPrcnt,T0.OpenQty ");
                query.Append(" ,VatSum ,T0.WhsCode ,DocEntry , LineNum ,LineStatus,OVTG.Name AS VatGroupName");
                query.Append(" FROM " + TableNameDefined.PURCHASE_ORDER_LINE + " T0 ");
                query.Append(" JOIN OWHS ON T0.WhsCode = OWHS.WhsCode ");
                query.Append(" JOIN OVTG ON OVTG.Code = T0.VatGroup ");
                query.Append(" WHERE  DocEntry = @Param1");
                dt = connect.Excute_Query(query.ToString(), lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
    }
}